﻿using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Text;
using IQToolkit;
using IQToolkit.Data;
using IQToolkit.Data.SqlClient;
using SweetSQL.Data;
using SweetSQL.Extensions;
using IQTool = IQToolkit.Data.Mapping;
using Linq = System.Data.Linq.Mapping;
using Sweet = SweetSQL.Data;
using SweetEx = SweetSQL.Extensions;

namespace SweetSQL.Test
{
    public static class Program
    {
        private enum Test
        {
            Insert,
            Select,
            SelectAll,
            Update,
            Delete,
            DeleteAll,
            CreateTable,
            DropTable,
            Perf,
            Where,
            Where_Local,
            Where_Instance,
        }

        [Linq.Table(Name = "Employees")]
        private sealed class Employee
        {
            [Linq.Column(IsPrimaryKey = true, CanBeNull = false, DbType = "UniqueIdentifier NOT NULL", Name = "ID", UpdateCheck = UpdateCheck.Never)]
            public Guid ID
            {
                get;
                set;
            }

            [Linq.Column(Name = "Name", CanBeNull = false, DbType = "NVarChar(50) NOT NULL", UpdateCheck = UpdateCheck.Never)]
            public string Name
            {
                get;
                set;
            }

            [Linq.Column(Name = "Number", CanBeNull = false, DbType = "Int NOT NULL", UpdateCheck = UpdateCheck.Never)]
            public int Number
            {
                get;
                set;
            }

            public override string ToString()
            {
                return string.Format("ID: {0} Name: {1} Number: {2}", ID, Name, Number);
            }
        }

        private sealed class SweetDatabase : DatabaseEx<SweetDatabase>
        {
            public SweetDatabase(ConnectionParameters parameters)
                : base(parameters)
            { }

            [Sweet.Table(Name = "Employees")]
            [Sweet.Column(IsPrimaryKey = true, MemberName = "ID")]
            [Sweet.Column(MemberName = "Name")]
            [Sweet.Column(MemberName = "Number")]
            public SweetEx.TableEx<Employee> Employees
            {
                get
                {
                    return GetTableEx<Employee>("Employees");
                }
            }
        }

        private sealed class LinqDatabase : DataContext
        {
            public System.Data.Linq.Table<Employee> Employees = null;

            public LinqDatabase(string connectString)
                : base(connectString)
            { }
        }

        private sealed class IQToolkitDatabase
        {
            private IEntityProvider _provider = null;

            public IQToolkitDatabase(IEntityProvider provider)
            {
                _provider = provider;
            }

            [IQTool.Table(Name = "Employees")]
            [IQTool.Column(Name = "ID", IsPrimaryKey = true, DbType = "UniqueIdentifier NOT NULL")]
            [IQTool.Column(Name = "Name", DbType = "NVarChar(50) NOT NULL")]
            [IQTool.Column(Name = "Number", DbType = "Int NOT NULL")]
            public IEntityTable<Employee> Employees
            {
                get { return _provider.GetTable<Employee>("Employees"); }
            }
        }

        private sealed class TestInfo
        {
            public ConnectionParameters Parameters
            {
                get;
                set;
            }

            public string Name
            {
                get;
                set;
            }
        }

        private static Guid _jonId = Guid.NewGuid();

        private static void Main(string[] args)
        {
            //Comments
            //I had to download specific providers for many of these to work
            //I also had "real" servers running for these tests
            //SQLite is probably the only one that will run straight up without you doing anything
            //Merely comment out TestInfo instances in testInfo to remove tests for some providers

            string windowsPassword = "",
                linuxPassword = "";

            Console.WriteLine("Enter Windows Password:");
            windowsPassword = Console.ReadLine();
            Console.WriteLine("Enter Linux Password:");
            linuxPassword = Console.ReadLine();

            try
            {
                const string WIN_IP = "192.168.134.64",
                    LINUX_IP = "192.168.134.65",
                    DATABASE = "Test",
                    WIN_USER = "sa",
                    LINUX_USER = "root",
                    PSQL_IP = "192.168.100.1";

                TestInfo[] testInfo = new TestInfo[]
                    {
                        new TestInfo 
                            {
                                Name = "SQL Server",
                                Parameters = new ConnectionParameters
                                    {
                                        DataSource = WIN_IP,
                                        Database = DATABASE,
                                        Username = WIN_USER,
                                        Password = windowsPassword,
                                        ConnectionType = ConnectionType.SqlServer,
                                        SyntaxType = SyntaxType.SqlServer,
                                    }
                            },
                        new TestInfo 
                            {
                                Name = "SQL Server, (minus lazy init)",
                                Parameters = new ConnectionParameters
                                    {
                                        DataSource = WIN_IP,
                                        Database = DATABASE,
                                        Username = WIN_USER,
                                        Password = windowsPassword,
                                        ConnectionType = ConnectionType.SqlServer,
                                        SyntaxType = SyntaxType.SqlServer,
                                    }
                            },
                        new TestInfo 
                            {
                                Name = "Oledb to SQL Server",
                                Parameters = new ConnectionParameters
                                    {
                                        DataSource = WIN_IP,
                                        Database = DATABASE,
                                        Username = WIN_USER,
                                        Password = windowsPassword,
                                        Provider = "SQLOLEDB",
                                        ConnectionType = ConnectionType.OleDb,
                                        SyntaxType = SyntaxType.Open,
                                    }
                            },
                        new TestInfo 
                            {
                                Name = "Odbc to SQL Server",
                                Parameters = new ConnectionParameters
                                    {
                                        DataSource = WIN_IP,
                                        Database = DATABASE,
                                        Username = WIN_USER,
                                        Password = windowsPassword,
                                        Provider = "SQL Server",
                                        ConnectionType = ConnectionType.Odbc,
                                        SyntaxType = SyntaxType.Open,
                                    }
                            },
                        new TestInfo 
                            {
                                Name = "MySQL",
                                Parameters = new ConnectionParameters
                                    {
                                        DataSource = LINUX_IP,
                                        Database = DATABASE,
                                        Username = LINUX_USER,
                                        Password = linuxPassword,
                                        ConnectionType = ConnectionType.MySql,
                                        SyntaxType = SyntaxType.MySql,
                                    }
                            },
                        //Still messing with these 2 they are broken right now
                        //new TestInfo 
                        //    {
                        //        //http://cherrycitysoftware.com/CCS/Providers/ProvMySQL.aspx 
                        //        Name = "Oledb to MySQL",
                        //        Parameters = new ConnectionParameters
                        //            {
                        //                DataSource = LINUX_IP,
                        //                Database = DATABASE,
                        //                Username = LINUX_USER,
                        //                Password = linuxPassword,
                        //                Provider = "MySQL Provider",
                        //                ConnectionType = ConnectionType.OleDb,
                        //                SyntaxType = SyntaxType.MySql,
                        //            }
                        //    },
                        //new TestInfo 
                        //    {
                        //        //http://dev.mysql.com/downloads/connector/odbc/5.1.html
                        //        Name = "Odbc to MySQL",
                        //        Parameters = new ConnectionParameters
                        //            {
                        //                DataSource = LINUX_IP,
                        //                Database = DATABASE,
                        //                Username = LINUX_USER,
                        //                Password = linuxPassword,
                        //                Provider = "MySQL ODBC 5.1 Driver",
                        //                ConnectionType = ConnectionType.Odbc,
                        //                SyntaxType = SyntaxType.MySql,
                        //            }
                        //    },
                        new TestInfo 
                            {
                                Name = "SQLite",
                                Parameters = new ConnectionParameters
                                    {
                                        DataSource = "Test.db",
                                        ConnectionType = ConnectionType.SQLite,
                                        SyntaxType = SyntaxType.SQLite,
                                    }
                            },
                        //new TestInfo 
                        //    {
                        //        Name = "Pervasive SQL",
                        //        Parameters = new ConnectionParameters
                        //            {
                        //                DataSource = PSQL_IP,
                        //                Database = DATABASE,
                        //                ConnectionType = ConnectionType.Psql,
                        //                SyntaxType = SyntaxType.Psql,
                        //            }
                        //    },
                    };

                //Test[] tests = new Test[] { Test.DeleteAll, Test.Perf };
                //Test[] tests = new Test[] { Test.DeleteAll, Test.Insert, Test.SelectAll, Test.Update, Test.Select, Test.Delete, Test.SelectAll };
                Test[] tests = new Test[] { Test.DeleteAll, Test.Insert, Test.Where, Test.Where_Local, Test.Where_Instance };
                const bool runPerfComparison = false;

                foreach (var info in testInfo)
                {
                    Console.WriteLine("***** " + info.Name + " *****");
                    DateTime start = DateTime.Now;
                    using (SweetDatabase database = new SweetDatabase(info.Parameters))
                    {
                        database.BeginTransaction();

                        foreach (Test test in tests)
                        {
                            RunTest(test, database);
                        }

                        database.Commit();
                    }
                    Console.WriteLine(info.Name + " took: " + (DateTime.Now - start));
                }

                if (runPerfComparison)
                {
                    Console.WriteLine();
                    Console.WriteLine("******** Running framework comparison tests ********");

                    //Let's try Linq to SQL!
                    Console.WriteLine("Linq.....");
                    DateTime start = DateTime.Now;
                    using (LinqDatabase database = new LinqDatabase("Data Source=192.168.134.64;Initial Catalog=Test;User ID=sa;Password=@utopiawin!"))
                    {
                        database.ExecuteCommand("DELETE FROM [Employees]");

                        for (int i = 0; i < 10000; i++)
                        {
                            database.Employees.InsertOnSubmit(new Employee
                            {
                                ID = Guid.NewGuid(),
                                Name = "NAME " + i,
                                Number = i,
                            });
                        }

                        database.SubmitChanges();
                    }
                    Console.WriteLine("Linq took: " + (DateTime.Now - start));

                    //Let's try IQToolkit, open source on codeplex as well
                    //I am not entirely sure why this is so slow
                    Console.WriteLine("IQToolkit.....");
                    start = DateTime.Now;
                    {
                        //This is the only way I could figure out a connect string
                        using (var connection = new System.Data.SqlClient.SqlConnection("Data Source=192.168.134.64;Initial Catalog=Test;User ID=sa;Password=@utopiawin!"))
                        {
                            connection.Open();
                            SqlQueryProvider provider = new SqlQueryProvider(connection, new IQToolkit.Data.Mapping.ImplicitMapping(new IQToolkit.Data.SqlClient.TSqlLanguage()));
                            provider.Transaction = connection.BeginTransaction();
                            IQToolkitDatabase database = new IQToolkitDatabase(provider);
                            for (int i = 0; i < 10000; i++)
                            {
                                database.Employees.Insert(new Employee
                                    {
                                        ID = Guid.NewGuid(),
                                        Name = "NAME " + i,
                                        Number = i,
                                    });
                            }
                            provider.Transaction.Commit();
                        }
                    }
                    Console.WriteLine("IQToolkit took: " + (DateTime.Now - start));
                }
            }
            catch (Exception exc)
            {
                Console.WriteLine(exc);
            }

            Console.WriteLine("DONE! Press enter to quit.");
            Console.ReadLine();
        }

        private static void RunTest(Test test, SweetDatabase database)
        {
            Console.WriteLine("Running {0} test...", test);

            switch (test)
            {
                case Test.CreateTable:
                    database.Employees.Create();
                    break;
                case Test.DropTable:
                    database.Employees.Drop();
                    break;
                case Test.Insert:
                    database.Employees.BulkInsert(new Employee[]
                        {
                            new Employee
                                {
                                    ID = _jonId,
                                    Name = "Jonathan Peppers",
                                    Number = 2342234,
                                },
                            new Employee
                                {
                                    ID = Guid.NewGuid(),
                                    Name = "Michael Scott",
                                    Number = 23423377,
                                },
                            new Employee
                                {
                                    ID = Guid.NewGuid(),
                                    Name = "Jim Halpert",
                                    Number = 853356,
                                },
                        });
                    break;
                case Test.Select:
                    Console.WriteLine(database.Employees.Select(new Employee { ID = _jonId }));
                    break;
                case Test.SelectAll:
                    foreach (Employee employee in database.Employees)
                    {
                        Console.WriteLine(employee);
                    }
                    break;
                case Test.Where:
                    foreach (Employee employee in database.Employees.Where(e => e.ID == _jonId))
                    {
                        Console.WriteLine(employee);
                    }
                    break;
                case Test.Where_Local:
                    {
                        Guid localVar = _jonId;
                        foreach (Employee employee in database.Employees.Where(e => e.ID == localVar))
                        {
                            Console.WriteLine(employee);
                        }
                    }
                    break;
                case Test.Where_Instance:
                    {
                        var instance = new
                            {
                                ID = _jonId,
                            };
                        foreach (Employee employee in database.Employees.Where(e => e.ID == instance.ID))
                        {
                            Console.WriteLine(employee);
                        }
                    }
                    break;
                case Test.Update:
                    database.Employees.Update(new Employee { ID = _jonId, Name = "Sweet Johnny", Number = 1111 });
                    break;
                case Test.Delete:
                    database.Employees.Delete(new Employee { ID = _jonId });
                    break;
                case Test.DeleteAll:
                    database.Employees.Delete();
                    break;
                case Test.Perf:
                    database.Employees.BulkInsert(EmployeeGenerator());
                    break;
                default:
                    break;
            }

            Console.WriteLine("Done with {0} test...", test);
        }

        private static IEnumerable<Employee> EmployeeGenerator()
        {
            for (int i = 0; i < 10000; i++)
            {
                yield return new Employee
                    {
                        ID = Guid.NewGuid(),
                        Name = "NAME " + i,
                        Number = i,
                    };
            }
        }
    }
}